package com.sxfq.loansupermarket.backsystem.modular.basicData.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.annotion.log.BussinessLog;
import com.sxfq.loansupermarket.backsystem.common.constant.Dict;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.ConstantFactory;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.constant.tips.Tip;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.common.node.ZTreeNode;
import com.sxfq.loansupermarket.backsystem.common.persistence.dao.MPayServiceMapper;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.PayService;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.LogObjectHolder;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.dao.PayServiceDao;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.SearchPayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.service.IPayServiceService;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.CaseInfo;
import com.sxfq.loansupermarket.backsystem.modular.system.warpper.ChannelWarpper;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.ConsultantInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.DealInfo;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * 渠道管理
 *
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/payService")
public class PayServiceController extends BaseController {

    @Autowired
    IPayServiceService service;

    @Autowired
    ICustomerService customerService;

    @Autowired
    private PayServiceDao dao;

    @Autowired
    MPayServiceMapper mPayServiceMapper;


    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/basicData/payService/";

    /**
     * 跳转到渠道管理页面
     */
    @RequestMapping("")
    public String payService(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "/payService.html";
    }

    /**
     * 跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/payService_add")
    public String payServiceAdd() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "payService_add.html";
    }

    @RequestMapping(value = "/payServiceImprot")
    public String payServiceImprot() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "payService_improt.html";
    }

    /**
     * 删除
     */
    @BussinessLog(value = "删除项目", key = "payServiceId", dict = Dict.DeleteDict)
    @RequestMapping(value = "/delete")
    @ResponseBody
    public Object delete(@RequestParam Integer payServiceId) {
        //缓存被删除的渠道名称
        LogObjectHolder.me().set(ConstantFactory.me().getPaySerivceName(payServiceId));
        service.deleteChannel(payServiceId);
        return SUCCESS_TIP;
    }

    /**
     * 跳转到渠道管理详情页面
     */
    @RequestMapping(value = "/payService_update/{payServiceId}")
    public String payServiceUpdate(@PathVariable String payServiceId, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(payServiceId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        PayServiceInfo info = service.selectOneById(payServiceId);
        model.addAttribute("payServiceInfo", info);
        return PREFIX + "payService_edit.html";
    }


    /**
     * 获取渠道的tree列表
     */
    @RequestMapping(value = "/tree")
    @ResponseBody
    public List<ZTreeNode> tree() {
        List<ZTreeNode> tree = this.dao.tree();
        tree.add(ZTreeNode.createParent());
        return tree;
    }

    /**
     * 渠道管理列表
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object list(String condition) {
        List<Map<String, Object>> list = this.dao.list(condition);
        return super.warpObject(new ChannelWarpper(list));
    }

    /**
     * 修改
     */
    @RequestMapping(value = "/modifying_data/{cir}")
    public String modifying_data(@PathVariable String cir, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String customerId = cir.split(",")[0];
        String id2 = cir.split(",")[1];
        String reservationsId = cir.split(",")[2];
        String operationTime = cir.split(",")[3];
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }

        String tableName1 = tableHead + "deal_info";
        String sql = "select * from " + tableName1 + " where id='" + id2 + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql, ConsultantInfo.class);


        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("customerId", customerId);
        model.addAttribute("id2", id2);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("operationTime", operationTime);
        return PREFIX + "modifying_data.html";
    }

    /**
     * 新增收费项目
     */
    @BussinessLog(value = "添加收费项目", key = "payServiceName", dict = Dict.PaySerivceDict)
    @RequestMapping(value = "/add")
    @ResponseBody
    public Object add(PayService info) {
        if (ToolUtil.isOneEmpty(info, info.getPayservicename())) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        //完善pids,根据pid拿到pid的pids
        channelSetPids(info);
        return this.mPayServiceMapper.insert(info);
    }

    private void channelSetPids(PayService info) {
        if (ToolUtil.isEmpty(info.getPid()) || "0".equals(info.getPid() + "")) {
            info.setPid(0);
            info.setPids("[0],");
        } else {
            int pid = info.getPid();
            PayService temp = mPayServiceMapper.selectById(pid);
            String pids = temp.getPids();
            info.setPid(pid);
            info.setPids(pids + "[" + pid + "],");
        }
    }

    @RequestMapping("/payServicelist")
    @ResponseBody
    public Object testrInfoList(SearchPayServiceInfo info, HttpServletRequest request) {
        Page<PayServiceInfo> page = new PageFactory<PayServiceInfo>().defaultPage();
        try {
            List<PayServiceInfo> list = service.selectPageListByQuery(page, info);
            page.setRecords(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 添加预约客户
     */
    @RequestMapping(value = "/addPayService")
    @ResponseBody
    public int add(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        return service.insert(info);
    }

    @RequestMapping(value = "/updatePayService")
    @ResponseBody
    public int update(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        return service.updatePayService(info);
    }

    /**
     * 删除预约客户
     */
    @RequestMapping(value = "/deletePayService/{id}")
    @ResponseBody
    public Tip delete(@PathVariable String id) throws Exception {
        service.delete(id);
        return SUCCESS_TIP;
    }

    /**
     * 删除
     */
    @RequestMapping(value = "/detail/{id}")
    @ResponseBody
    public Object detail(@PathVariable("id") Integer id) {
        return mPayServiceMapper.selectById(id);
    }

    /**
     * 修改
     */
    @BussinessLog(value = "修改收费项目", key = "payServiceName", dict = Dict.PaySerivceDict)
    @RequestMapping(value = "/update")
    @ResponseBody
    public Object update(PayService payService) {
        if (ToolUtil.isEmpty(payService) || payService.getId() == null) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        channelSetPids(payService);
        mPayServiceMapper.updateById(payService);
        return super.SUCCESS_TIP;
    }


    @RequestMapping(value = "/getList", method = RequestMethod.POST)
    @ResponseBody
    public List<Map<String, Object>> getList() {
        List<Map<String, Object>> list = service.list();
        return list;
    }


    @RequestMapping(value = "/cancelBill")
    @ResponseBody
    public int delete_openBill(@Valid DealInfo info, BindingResult result) throws Exception {
        String sql1 = "delete from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
        return sqlMapper.delete(sql1);
    }

    /**
     * 新增项目窗口
     *
     * @param id
     * @param model
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/getPayServiceDetails/{id}")
    public String getPayServiceDetails(@PathVariable String id, Model model) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        PayServiceInfo payServiceInfo = service.selectOneById(id);
        model.addAttribute("payServiceInfo", payServiceInfo);
        ShiroKit.setSessionAttr("customerId", ShiroKit.getSessionAttr("customerId"));
        model.addAttribute("customerId", ShiroKit.getSessionAttr("customerId"));
        return PREFIX + "payService_details.html";
    }

    /**
     * 修改项目新增项目窗口
     *
     * @param
     * @param model
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/getPayServiceUpdateAdd/{cri}")
    public String getPayServiceUpdateAdd(@PathVariable String cri, Model model) throws Exception {
        String id = cri.split(",")[0];
        String customerId = cri.split(",")[1];
        String reservationsId = cri.split(",")[2];
        String operationTime = cri.split(",")[3];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        PayServiceInfo payServiceInfo = service.selectOneById(id);
        model.addAttribute("payServiceInfo", payServiceInfo);

        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("operationTime", operationTime);
        ShiroKit.setSessionAttr("customerId", ShiroKit.getSessionAttr("customerId"));
        model.addAttribute("customerId", ShiroKit.getSessionAttr("customerId"));
        return PREFIX + "getPayServiceUpdateAdd.html";
    }

    @RequestMapping(value = "/getPayServiceDetailss/{ci}")
    public String getPayServiceDetailss(@PathVariable String ci, Model model) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String customerId = ci.split(",")[0];
        String id = ci.split(",")[1];
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        PayServiceInfo payServiceInfo = service.selectOneById(id);
        model.addAttribute("payServiceInfo", payServiceInfo);
        model.addAttribute("customerId", customerId);
        return PREFIX + "payService_detailss.html";
    }

    @RequestMapping(value = "/getPayServiceDetailssd/{ci}")
    public String getPayServiceDetailssd(@PathVariable String ci, Model model) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String customerId = ci.split(",")[0];
        String id = ci.split(",")[1];
        String reservationsId = ci.split(",")[2];
        String operationTime = ci.split(",")[3];
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        PayServiceInfo payServiceInfo = service.selectOneById(id);
        model.addAttribute("payServiceInfo", payServiceInfo);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("operationTime", operationTime);
        return PREFIX + "payService_detailssd.html";
    }

    @RequestMapping(value = "/getPayServiceDetail/{ci}")
    public String getPayServiceDetail(@PathVariable String ci, Model model) throws Exception {
        String customerId = ci.split(",")[0];
        String id = ci.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select * from open_bill_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where t1.id=" + id + " and customer_id='" + customerId + "'";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);
        model.addAttribute("customerId", customerId);
        payServiceInfo.setId(id);
        model.addAttribute("payServiceInfo", payServiceInfo);
        return PREFIX + "payService_detail.html";
    }

    @RequestMapping(value = "/getDealInfoDetail/{ci}")
    public String getDealInfoDetail(@PathVariable String ci, Model model) throws Exception {
        if (ToolUtil.isEmpty(ci)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String customerId = ci.split(",")[0];
        String id = ci.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "deal_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from " + tableName + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where t1.id='" + id + "' and customer_id='" + customerId + "'";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);
        model.addAttribute("customerId", customerId);
        payServiceInfo.setId(id);
        model.addAttribute("payServiceInfo", payServiceInfo);
        return PREFIX + "dealInfo_detail.html";
    }

    //修改开单项目提交
    @RequestMapping(value = "/updateOk", method = RequestMethod.POST)
    @ResponseBody
    public int updateOk(String[] str,String remarkss,HttpServletRequest request) {
        String id = "";
        String serviceActualPay = "";
        String foldingPay = "";

        for (int i = 0; i < str.length; i++) {
            id = str[i].split("#")[0];
            serviceActualPay = str[i].split("#")[1];
            foldingPay = str[i].split("#")[2];
            Double arrears=sub(Double.parseDouble(foldingPay),Double.parseDouble(serviceActualPay));
            String tableHead = ShiroKit.getUser().getAbbreviation();
            String tableName = tableHead + "deal_info";
            String sql1 = "update " + tableName + " a set a.folding_pay=" + foldingPay + ",a.service_actual_pay=" + serviceActualPay + ",a.arrears="+arrears+"" +
                    ",remarks='"+remarkss+"' where a.id='" + id + "'";
            sqlMapper.update(sql1);
        }
        return 1;
    }

    public static double sub(double d1,double d2){
        BigDecimal bd1 = new BigDecimal(Double.toString(d1));
        BigDecimal bd2 = new BigDecimal(Double.toString(d2));
        return bd1.subtract(bd2).doubleValue();
    }

    @RequestMapping(value = "/deleteDealInfos", method = RequestMethod.POST)
    @ResponseBody
    public int deleteDealInfos(String id2, Model model) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "deal_info";
        String tableName1 = tableHead + "recharge_info";
        String sql = "select id as id,id2 as id2,reservations_id as reservationsId,  (folding_pay - sum(service_actual_pay))  as arrears, " +
                "sum(service_actual_pay) as serviceActualPay," +
                "get_time as getTime  from " + tableName + " where id2='" + id2 + "'  ";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);

        //最后一次操作得到欠费
        String sql3 = "select id as id,id2 as id2,reservations_id as reservationsId, arrears, " +
                " service_actual_pay  as serviceActualPay," +
                "get_time as getTime  from " + tableName + " where id2='" + id2 + "' order by getTime   desc limit 0,1  ";
        PayServiceInfo payServiceInfo3 = sqlMapper.selectOne(sql3, PayServiceInfo.class);

        String deletesql = "delete from " + tableName + " where id2='" + id2 + "'";
        String deletesql3 = "delete from " + tableName1 + " where reservations_id='" + payServiceInfo.getReservationsId() + "' and id2='" + payServiceInfo.getId2() + "' and operation_time='" + payServiceInfo.getGetTime() + "'";
        String deletesql1 = "update aggregate_info set money=money-'"+payServiceInfo.getServiceActualPay()+"' where reservations_id='" + payServiceInfo.getReservationsId() + "' and left(get_time,10)='" + payServiceInfo.getGetTime().substring(0, 10) + "'";
        String deletesql2 = "delete from reservations_aggregate_info where reservations_id='" + payServiceInfo.getReservationsId() + "' and money=" + payServiceInfo.getServiceActualPay() + " and left(get_time,10)='" + payServiceInfo.getGetTime().substring(0, 10) + "'";
        if (payServiceInfo != null) {
            String updateSql2 = "update reservations_info set money=money-'" + payServiceInfo.getServiceActualPay() + "' where id='" + payServiceInfo.getReservationsId() + "' ";
            String updateSql1 = "update " + this.getTableName() + " a set a.actual_account_balance=actual_account_balance-'" + payServiceInfo.getServiceActualPay() + "'," +
                    "account_arrears=account_arrears-'" + payServiceInfo3.getArrears() + "' where reservations_id='" + payServiceInfo.getReservationsId() + "'";
            sqlMapper.update(updateSql1);
            sqlMapper.update(updateSql2);
        }
        sqlMapper.delete(deletesql1);
        sqlMapper.delete(deletesql2);
        sqlMapper.delete(deletesql3);
        return sqlMapper.delete(deletesql);
    }

    @RequestMapping(value = "/getFirstLevelProject", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServiceInfo> getFirstLevelProject(String dc, Model model) throws Exception {
        if (ToolUtil.isEmpty(dc.split(",")[0])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(dc.split(",")[1])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        List<PayServiceInfo> payServiceInfoList = service.selectListByDepartment(dc.split(",")[0]);
        for (PayServiceInfo info : payServiceInfoList) {
            info.setCustomerId(dc.split(",")[1]);
        }
        return payServiceInfoList;
    }

    @RequestMapping(value = "/deleteOpenBill", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServiceInfo> deleteOpenBill(String id, Model model) throws Exception {
        String deletesql = "delete from open_bill_info where id='" + id + "'";
        sqlMapper.delete(deletesql);
        String sql = "select * from open_bill_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
        List<PayServiceInfo> list = sqlMapper.selectList(sql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        for (PayServiceInfo payServiceInfo : list) {
            totleReceivable += (payServiceInfo.getPayServiceNum()) * (payServiceInfo.getPayServicePriceTwo());
            totleFoldingPay += payServiceInfo.getFoldingPay();
            payServiceInfo.setTotleReceivable(totleReceivable);
            payServiceInfo.setTotleFoldingPay(totleFoldingPay);
        }
        return list;
    }


    @RequestMapping(value = "/getPayServiceId", method = RequestMethod.POST)
    @ResponseBody
    public PayServiceInfo getPayServiceId(String df, Model model) throws Exception {
        if (ToolUtil.isEmpty(df.split(",")[0])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(df.split(",")[1])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String department = df.split(",")[0];
        String firstLevelProject = df.split(",")[1];
        String sql = "select * from pay_service_info a where a.department='" + department + "' and a.first_level_project='" + firstLevelProject + "' limit 0,1";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);
        return payServiceInfo;
    }

    @RequestMapping(value = "/getPaySeriveName", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServiceInfo> getPaySeriveName(String cdf, Model model) throws Exception {
        if (ToolUtil.isEmpty(cdf.split(",")[0])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(cdf.split(",")[1])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(cdf.split(",")[2])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String firstLevelProject = cdf.split(",")[2];
        String department = cdf.split(",")[1];
        String customerId = cdf.split(",")[0];
        List<PayServiceInfo> payServiceInfoList = service.selectListByFirstLevelProject(department, firstLevelProject);
        model.addAttribute("customerId", customerId);
        model.addAttribute("department", department);
        model.addAttribute("firstLevelProject", firstLevelProject);
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        return payServiceInfoList;
    }

    @RequestMapping(value = "/getPayServiceList", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServiceInfo> getPayServiceList(String payServiceNames, Double payServicePrices, Model model) throws Exception {
        String sql = "select * from ( select * from pay_service_info a where 1=1 ";
        if (payServiceNames != null && payServiceNames != "") {
            sql += "and  a.pay_service_name like CONCAT('%','" + payServiceNames + "','%') ";
        }
        sql += " )t ";
        if (!ToolUtil.isEmpty(payServicePrices)) {
            sql += "where t.pay_service_price_one=" + payServicePrices + " or " +
                    "t.pay_service_price_two=" + payServicePrices + " ";
        }
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServiceInfo.class);
        return payServiceInfoList;
    }

    @RequestMapping(value = "/to_getPayServiceLists/{npcr}")
    public String to_getPayServiceLists(Model model, @PathVariable String npcr) throws Exception {
        String payServiceNames = npcr.split(",")[0];
        String payServicePrices = npcr.split(",")[1];
        String customerId = npcr.split(",")[2];
        String reservationsId = npcr.split(",")[3];
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = " select * from pay_service_info a where  ";
        if (payServiceNames != null && payServiceNames != "") {
            sql += "(a.pay_service_name like ('%" + payServiceNames + "%') or firstconcat(pay_service_name) like ('%" + payServiceNames + "%')) ";
        }
        if (!ToolUtil.isEmpty(payServicePrices)) {
            sql += "and (a.pay_service_price_one=" + payServicePrices + " or " +
                    "a.pay_service_price_two=" + payServicePrices + ") ";
        }
        CustomerInfo customerInfo = customerService.selectOneByReservationsId(reservationsId);
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServiceInfo.class);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        return PREFIX + "payServiceInfoList.html";
    }

    @RequestMapping(value = "/to_getPayServiceListsd/{npcr}")
    public String to_getPayServiceListsd(Model model, @PathVariable String npcr) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String payServiceNames = npcr.split(",")[0];
        String payServicePrices = npcr.split(",")[1];
        String customerId = npcr.split(",")[2];
        String reservationsId = npcr.split(",")[3];
        String operationTime = npcr.split(",")[4];
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = " select * from pay_service_info a where  ";
        if (payServiceNames != null && payServiceNames != "") {
            sql += "(a.pay_service_name like ('%" + payServiceNames + "%') or firstconcat(pay_service_name) like ('%" + payServiceNames + "%')) ";
        }
        if (!ToolUtil.isEmpty(payServicePrices)) {
            sql += "and (a.pay_service_price_one=" + payServicePrices + " or " +
                    "a.pay_service_price_two=" + payServicePrices + ") ";
        }
        CustomerInfo customerInfo = customerService.selectOneByReservationsId(reservationsId);
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServiceInfo.class);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("operationTime", operationTime);
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        return PREFIX + "payServiceInfoListd.html";
    }

    @RequestMapping(value = "/openUpdateMoney/{customerId}")
    public String openUpdateMoney(@PathVariable String customerId, Model model) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select sum(folding_pay) as foldingPay from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + customerId + "'";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);
        model.addAttribute("zheMoney", payServiceInfo.getFoldingPay());
        model.addAttribute("customerId", customerId);
        return PREFIX + "update_money.html";
    }

    //URL方式传值是直接把参数对应的值传过来比如URL 前端传的URL是 /openUpdateMoney1/123,3000,那么这个后台 的params=123,3000
    @RequestMapping(value = "/openUpdateMoney1/{params}")
    public String openUpdateMoney1(@PathVariable String params, Model model) throws Exception {
        String id = params.split(",")[0];
        String zheMoney = params.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select * from open_bill_info a left join pay_service_info b on a.pay_service_id=b.id\n" +
                " where a.id=" + id + "";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);
        model.addAttribute("lowestMoney", payServiceInfo.getPayServiceNum() * payServiceInfo.getPayServicePriceOne());
        model.addAttribute("highestMoney", payServiceInfo.getPayServiceNum() * payServiceInfo.getPayServicePriceTwo());
        model.addAttribute("id", id);
        model.addAttribute("zheMoney", zheMoney);
        return PREFIX + "update_money1.html";
    }

    @RequestMapping(value = "/openDealUpdateMoney/{params}")
    public String openUpdateMoney2(@PathVariable String params, Model model) throws Exception {
        String id = params.split(",")[0];
        String zheMoney = params.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "deal_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select * from " + tableName + "  a left join pay_service_info b on a.pay_service_id=b.id\n" +
                " where a.id='" + id + "'";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);
        model.addAttribute("lowestMoney", payServiceInfo.getPayServiceNum() * payServiceInfo.getPayServicePriceOne());
        model.addAttribute("highestMoney", payServiceInfo.getPayServiceNum() * payServiceInfo.getPayServicePriceTwo());
        model.addAttribute("id", id);
        model.addAttribute("zheMoney", zheMoney);
        return PREFIX + "updateDeal_money.html";
    }


    @RequestMapping(value = "/addPay")
    @ResponseBody
    public List<PayServiceInfo> addPay(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
//        String sql1 = "delete from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "' " +
//                " and pay_service_id='" + info.getId() + "'";
//        sqlMapper.delete(sql1);
        service.addPay(info);
        info.setPayServicePrice(service.selectPayService(info).getPayServicePrice());
        info.setReceivable((info.getPayServicePriceTwo()) * (info.getPayServiceNum()));
        String sql = "select * from open_bill_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
        List<PayServiceInfo> list = sqlMapper.selectList(sql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        for (PayServiceInfo payServiceInfo : list) {
            totleReceivable += (payServiceInfo.getPayServiceNum()) * (payServiceInfo.getPayServicePriceTwo());
            totleFoldingPay += payServiceInfo.getFoldingPay();
        }
        info.setTotleReceivable(totleReceivable);
        info.setTotleFoldingPay(totleFoldingPay);
        return list;
    }

    @RequestMapping(value = "/addPayd")
    @ResponseBody
    public List<PayServiceInfo> addPayd(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "deal_info";
        service.addPay(info);
        info.setPayServicePrice(service.selectPayService(info).getPayServicePrice());
        info.setReceivable((info.getPayServicePriceTwo()) * (info.getPayServiceNum()));
        String sql = "select * from "+tableName+" t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
        List<PayServiceInfo> list = sqlMapper.selectList(sql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        for (PayServiceInfo payServiceInfo : list) {
            totleReceivable += (payServiceInfo.getPayServiceNum()) * (payServiceInfo.getPayServicePriceTwo());
            totleFoldingPay += payServiceInfo.getFoldingPay();
        }
        info.setTotleReceivable(totleReceivable);
        info.setTotleFoldingPay(totleFoldingPay);
        return list;
    }

    @RequestMapping(value = "/addDealInfo", method = RequestMethod.POST)
    @ResponseBody
    public int addDealInfo(String id, String totalAmount, String customerId, String reservationsId, String payService, String payServiceNum,
                           String leftUp, String leftDown, String rightUp, String rightDown, String nurse,
                           String doctor, String referraler, String operationTime) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "deal_info";
        String ids = "";
        String sql1 = "select max(id) as id from " + tableName + " where operation_time='" + operationTime + "'";
        CaseInfo caseInfo = sqlMapper.selectOne(sql1, CaseInfo.class);
        String a1 = (operationTime.replace("'", "")).replace("-", "");
        String a2 = a1.replace(" ", "");
        String a3 = a2.replace(":", "");
        String a4 = tableHead.replace("_", "");
        if(caseInfo!=null){
            ids = a4 + (Integer.parseInt(caseInfo.getId().substring((ShiroKit.getUser().getAbbreviation().length()-2),(ShiroKit.getUser().getAbbreviation().length()+1)))+1) + a3;
        }else{
            ids=a4+"001"+a3;
        }
        String sql = "insert into " + tableName + " (`id`,  `is_do`, `customer_id`," +
                " `outpatient_name`, `reservations_id`, `pay_service_id`, `deal_status`, `tc_name`, " +
                "`pay_service_num`, `service_actual_pay`, `folding_pay`, `pay_service`, `arrears`, `operation_time`, " +
                "`get_time`, `left_up`, `left_down`, `right_up`, `right_down`, " +
                "`nurse`, `doctor`, `check_status`, `id2`, `referraler`) VALUES" +
                "('" + ids + "',5,'" + customerId + "', '"+ ShiroKit.getUser().getOutpatientName() +"','" + reservationsId + "','" + id + "','已开单','" + ShiroKit.getUser().getAccount() + "'," + payServiceNum + "," +
                "" + totalAmount + "," + totalAmount + "," + payService + ",0," +
                "'" + operationTime + "','" + operationTime + "','" + leftUp + "','" + leftDown + "'," +
                "'" + rightUp + "','" + rightDown + "','" + nurse + "','" + doctor + "',2,'" + ids + "','" + referraler + "')";
        return sqlMapper.insert(sql);
    }

    @RequestMapping(value = "/addPayUpdate")
    @ResponseBody
    public List<PayServiceInfo> addPayUpdate(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql1 = "delete from open_bill_info where id='" + info.getId() + "'";
        sqlMapper.delete(sql1);
        service.addPayUpdate(info);
        info.setPayServicePrice(service.selectPayServices(info).getPayServicePrice());
        info.setReceivable((info.getPayServicePriceTwo()) * (info.getPayServiceNum()));
        String sql = "select * from open_bill_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
        List<PayServiceInfo> list = sqlMapper.selectList(sql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        for (PayServiceInfo payServiceInfo : list) {
            totleReceivable += (payServiceInfo.getPayServiceNum()) * (payServiceInfo.getPayServicePriceTwo());
            totleFoldingPay += payServiceInfo.getFoldingPay();
        }
        info.setTotleReceivable(totleReceivable);
        info.setTotleFoldingPay(totleFoldingPay);
        return list;
    }

    @RequestMapping(value = "/addDealUpdate")
    @ResponseBody
    public List<PayServiceInfo> addDealUpdate(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "deal_info";
        String sql1 = "update " + tableName + " a set a.pay_service=" + info.getPayService() + ",a.pay_service_num=" + info.getPayServiceNum() + "," +
                "a.folding_pay=" + info.getPayServiceNum() * info.getPayService() + ",a.service_actual_pay=" + info.getPayServiceNum() * info.getPayService() + ",a.doctor='" + info.getDoctor() + "',a.nurse='" + info.getNurse() + "',a.referraler='" + info.getReferraler() + "'," +
                "a.left_up='" + info.getLeftUp() + "',a.left_down='" + info.getLeftDown() + "',a.right_up='" + info.getRightUp() + "',a.right_down='" + info.getRightDown() + "' " +
                "where a.id='" + info.getId() + "'";
        sqlMapper.update(sql1);
        info.setPayServicePrice(service.selectPayServices(info).getPayServicePrice());
        info.setReceivable((info.getPayServicePriceTwo()) * (info.getPayServiceNum()));
        String sql = "select * from " + tableName + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
        List<PayServiceInfo> list = sqlMapper.selectList(sql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        for (PayServiceInfo payServiceInfo : list) {
            totleReceivable += (payServiceInfo.getPayServiceNum()) * (payServiceInfo.getPayServicePriceTwo());
            totleFoldingPay += payServiceInfo.getFoldingPay();
        }
        info.setTotleReceivable(totleReceivable);
        info.setTotleFoldingPay(totleFoldingPay);
        return list;
    }

    @RequestMapping(value = "/deleteDealInfo", method = RequestMethod.POST)
    @ResponseBody
    public int deleteDealInfo(String id) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "deal_info";
        String sql1 = "delete from " + tableName + " where id='" + id + "'";
        return sqlMapper.delete(sql1);
    }

    @RequestMapping(value = "/updateMoney")
    @ResponseBody
    public List<PayServiceInfo> updateMoney(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        //修改当前行的折后金额
        Double updateMoney = info.getUpdateMoney();
        Double zheMoney = info.getZheMoney();
        String sql1 = "update open_bill_info a set a.folding_pay=folding_pay*" + updateMoney / zheMoney + " where a.customer_id='" + info.getCustomerId() + "'";
        sqlMapper.update(sql1);
        //查询当前修改 记录的客户对应所有的临时开单记录给前端
        String sql2 = "select * from open_bill_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where tc_name='" + ShiroKit.getUser().getAccount() + "' and t1.customer_id='" + info.getCustomerId() + "'";
        List<PayServiceInfo> list2 = sqlMapper.selectList(sql2, PayServiceInfo.class);
        return list2;
    }


    @RequestMapping(value = "/updateMoney1")
    @ResponseBody
    public List<PayServiceInfo> updateMoney1(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        //查询当前修改记录
        String sql = "select * from open_bill_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where tc_name='" + ShiroKit.getUser().getAccount() + "' and t1.id='" + info.getId() + "'";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);
        String customId = payServiceInfo.getCustomerId();

        //修改当前行的折后金额
        Double updateMoney = info.getUpdateMoney();
        String sql1 = "update open_bill_info a set a.folding_pay=" + updateMoney + " where a.id='" + info.getId() + "'";
        sqlMapper.update(sql1);

        //查询当前修改 记录的客户对应所有的临时开单记录给前端
        String sql2 = "select * from open_bill_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id where tc_name='" + ShiroKit.getUser().getAccount() + "' and t1.customer_id='" + customId + "'";
        List<PayServiceInfo> list2 = sqlMapper.selectList(sql2, PayServiceInfo.class);

        return list2;
    }


    @RequestMapping(value = "/updateDealMoney11/{cri}")
    @ResponseBody
    public int updateDealMoney11(@PathVariable String cri) throws Exception {
        String id = cri.split(",")[0];
        String updateMoney = cri.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName1 = tableHead + "deal_info";
        //修改当前行的折后金额
        String sql1 = "update " + tableName1 + " a set a.folding_pay=" + updateMoney + ",a.service_actual_pay=" + updateMoney + " where a.id='" + id + "'";
        return sqlMapper.update(sql1);
    }

    /**
     * 导入
     */
    @RequestMapping(value = "/upload")
    public String importTestQuestions(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
//                String sql = "insert into test_questions_info " +
//                        "( id, " + hssfRow0.getCell(0) + "," + hssfRow0.getCell(1) + "," + hssfRow0.getCell(2) + "" +
//                        "," + hssfRow0.getCell(3) + "," + hssfRow0.getCell(4) + "," + hssfRow0.getCell(5) + "" +
//                        "," + hssfRow0.getCell(6) + "," + hssfRow0.getCell(7) + "," + hssfRow0.getCell(8) + ",update_people,create_people,create_time,update_time) VALUES ";
                // 循环行Row ," + hssfRow0.getCell(8) + "
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    if (!StringUtil.isEmpty(xh1)) {
                        xh1s = xh1.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh2)) {
                        xh2s = xh2.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh3)) {
                        xh3s = xh3.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh4)) {
                        xh4s = xh4.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh5)) {
                        xh5s = xh5.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh6)) {
                        xh6s = xh6.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh7)) {
                        xh7s = xh7.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh8)) {
                        xh8s = xh8.toString().trim();
                    }
                    String sql = "insert into  pay_service_info " +
                            "(num,department,first_level_project,pay_service_name,pay_service_price_one,pay_service_price_two,unit) VALUES "
                            + "('" + xh2s + "','" + xh3s + "'" +
                            ",'" + xh4s + "','" + xh5s + "','" + xh6s + "','" + xh7s + "','" + xh8s + "');";
                    sqlMapper.insert(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "/testClose.html";
        } else {
            return "/testClose.html";
        }
    }

    @RequestMapping(value = "/openPayServiceDownLoadExcel")
    public void downLoad(HttpServletResponse response) {
        try {
            String sql = "select * from pay_service_info order by department";
            List<PayServiceInfo> list = sqlMapper.selectList(sql, PayServiceInfo.class);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("预约数据");

            for (int i = 0; i < 8; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("id");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("num");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("department");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("first_level_project");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("pay_service_name");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("pay_service_price_one");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("pay_service_price_two");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("unit");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("remarks");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                PayServiceInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                String num = map.getNum();
                String department = map.getDepartment();
                String firstLevelProject = map.getFirstLevelProject();
                String payServiceName = map.getPayServiceName();
                Double payServicePriceOne = map.getPayServicePriceOne();
                Double payServicePriceTwo = map.getPayServicePriceTwo();
                String unit = map.getUnit();
                String id = map.getId();
                String remarks = map.getRemarks();

                for (int j = 0; j <= 9; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(id);
                row.getCell(1).setCellValue(num);
                row.getCell(2).setCellValue(department);
                row.getCell(3).setCellValue(firstLevelProject);
                row.getCell(4).setCellValue(payServiceName);
                row.getCell(5).setCellValue(payServicePriceOne);
                row.getCell(6).setCellValue(payServicePriceTwo);
                row.getCell(7).setCellValue(unit);
                row.getCell(8).setCellValue(remarks);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getTableName() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return tableName;
    }

}
